DROP TABLE OBJTYPE CASCADE CONSTRAINTS;
DROP TABLE ATTRTYPE CASCADE CONSTRAINTS;
DROP TABLE OBJECTS CASCADE CONSTRAINTS;
DROP TABLE ATTRIBUTES CASCADE CONSTRAINTS;
DROP TABLE OBJREFERENCE CASCADE CONSTRAINTS;

DROP SEQUENCE SEQ_OBJECT_TYPE_ID;
DROP SEQUENCE SEQ_ATTR_ID;
DROP SEQUENCE SEQ_OBJECT_ID;

CREATE SEQUENCE SEQ_OBJECT_TYPE_ID
    INCREMENT BY 1  
    MINVALUE 1;

CREATE SEQUENCE SEQ_ATTR_ID
    INCREMENT BY 1  
    MINVALUE 1;

CREATE SEQUENCE SEQ_OBJECT_ID
    INCREMENT BY 1  
    MINVALUE 1;

--------------------------------------------------------------------------------
-- Таблица описаний объектных типов
CREATE TABLE OBJTYPE
  (
    OBJECT_TYPE_ID NUMBER(20) NOT NULL ENABLE,
    PARENT_ID      NUMBER(20),
    CODE           VARCHAR2(25) NOT NULL UNIQUE,
    NAME           VARCHAR2(200 BYTE),
    DESCRIPTION    VARCHAR2(1000 BYTE),
    CONSTRAINT CON_OBJECT_TYPE_ID PRIMARY KEY (OBJECT_TYPE_ID),
    CONSTRAINT CON_PARENT_ID FOREIGN KEY (PARENT_ID) REFERENCES OBJTYPE (OBJECT_TYPE_ID) ON DELETE CASCADE ENABLE
  );

COMMENT ON TABLE OBJTYPE IS 'Таблица описаний объектных типов'; 
COMMENT ON COLUMN OBJTYPE.OBJECT_TYPE_ID IS 'Идентификатор объектного типа';
COMMENT ON COLUMN OBJTYPE.PARENT_ID IS 'ссылка на идентификатор родительского объектного типа';
COMMENT ON COLUMN OBJTYPE.CODE IS 'название объектного типа в английской кодировке';
COMMENT ON COLUMN OBJTYPE.NAME IS 'название объектного типа в национальной кодировке (для GUI)';
COMMENT ON COLUMN OBJTYPE.DESCRIPTION IS 'разверное описание объектного типа в национальной кодировке (для GUI)';


-- Таблица описаний атрибутных типов
CREATE TABLE ATTRTYPE (
    ATTR_ID             NUMBER(20) NOT NULL ENABLE,
    OBJECT_TYPE_ID      NUMBER(20) NOT NULL ENABLE,
    OBJECT_TYPE_ID_REF  NUMBER(20),
    CODE                VARCHAR2(25),
    NAME                VARCHAR2(200 BYTE),
    CONSTRAINT CON_ATTR_ID PRIMARY KEY (ATTR_ID),
    CONSTRAINT CON_ATTR_OBJECT_TYPE_ID FOREIGN KEY (OBJECT_TYPE_ID) REFERENCES OBJTYPE (OBJECT_TYPE_ID) ENABLE,
    CONSTRAINT CON_ATTR_OBJECT_TYPE_ID_REF FOREIGN KEY (OBJECT_TYPE_ID_REF) REFERENCES OBJTYPE (OBJECT_TYPE_ID) ENABLE
);

COMMENT ON TABLE ATTRTYPE IS 'Таблица описаний атрибутных типов';
COMMENT ON COLUMN ATTRTYPE.OBJECT_TYPE_ID IS 'ссылка на идентификатор объектного типа класса, который характеризует данный атрибутный тип';
COMMENT ON COLUMN ATTRTYPE.OBJECT_TYPE_ID_REF 
    IS 'ссылка на идентификатор объектного типа класса, который для кратности "один-ко-многим" находится в отношении "один"
    при отношении "много", в котором находится объектный тип класса из ATTRTYPE.OBJECT_TYPE_ID';
COMMENT ON COLUMN ATTRTYPE.CODE IS 'название атрибутного типа в английской кодировке';
COMMENT ON COLUMN ATTRTYPE.NAME IS 'название атрибутного типа в национальной кодировке (для GUI)';



-- Таблица описаний экземпляров объектов
CREATE TABLE OBJECTS (
    OBJECT_ID      NUMBER(20) NOT NULL ENABLE,
    PARENT_ID      NUMBER(20),
    OBJECT_TYPE_ID NUMBER(20) NOT NULL ENABLE,
    NAME           VARCHAR2(2000 BYTE),
    DESCRIPTION    VARCHAR2(4000 BYTE),
    CONSTRAINT CON_OBJECTS_ID PRIMARY KEY (OBJECT_ID),
    CONSTRAINT CON_PARENTS_ID FOREIGN KEY (PARENT_ID) REFERENCES OBJECTS (OBJECT_ID) ON DELETE CASCADE DEFERRABLE ENABLE,
    CONSTRAINT CON_OBJ_TYPE_ID FOREIGN KEY (OBJECT_TYPE_ID) REFERENCES OBJTYPE (OBJECT_TYPE_ID) ENABLE
);


CREATE TABLE ATTRIBUTES
  (
    ATTR_ID    NUMBER(20) NOT NULL ENABLE,
    OBJECT_ID  NUMBER(20) NOT NULL ENABLE,
    VALUE      VARCHAR2(4000 BYTE),
    DATE_VALUE DATE,
    CONSTRAINT CON_ATTRIBUTES_PK PRIMARY KEY (ATTR_ID,OBJECT_ID),
    CONSTRAINT CON_AOBJECT_ID FOREIGN KEY (OBJECT_ID) REFERENCES OBJECTS (OBJECT_ID) ON DELETE CASCADE ENABLE,
    CONSTRAINT CON_AATTR_ID FOREIGN KEY (ATTR_ID) REFERENCES ATTRTYPE (ATTR_ID) ON DELETE CASCADE ENABLE
  );  

COMMENT ON TABLE ATTRIBUTES IS 'Таблица описаний атрибутов экземпляров объектов';
COMMENT ON COLUMN ATTRIBUTES.VALUE IS 'Значение атрибута экземпляра объекта в виде строки или числа';
COMMENT ON COLUMN ATTRIBUTES.DATE_VALUE IS 'Значение атрибута экземпляра объекта в виде даты';



-- Таблица описаний связей "простая ассоциация" между экземплярами объектов
CREATE TABLE OBJREFERENCE
  (
    ATTR_ID   NUMBER(20) NOT NULL ENABLE,
    REFERENCE NUMBER(20) NOT NULL ENABLE,
    OBJECT_ID NUMBER(20) NOT NULL ENABLE,
    CONSTRAINT CON_OBJREFERENCE_PK PRIMARY KEY (ATTR_ID,REFERENCE,OBJECT_ID),
    CONSTRAINT CON_REFERENCE FOREIGN KEY (REFERENCE) REFERENCES OBJECTS (OBJECT_ID) ON DELETE CASCADE ENABLE,
    CONSTRAINT CON_ROBJECT_ID FOREIGN KEY (OBJECT_ID) REFERENCES OBJECTS (OBJECT_ID) ON DELETE CASCADE ENABLE,
    CONSTRAINT CON_RATTR_ID FOREIGN KEY (ATTR_ID) REFERENCES ATTRTYPE (ATTR_ID) ON DELETE CASCADE ENABLE
  ); 

COMMENT ON TABLE OBJREFERENCE IS 'Таблица описаний связей между экземплярами объектов';
COMMENT ON COLUMN OBJREFERENCE.ATTR_ID IS 'ссылка на атрибутный тип как ассоциативная связь между экземплярами объектов';
COMMENT ON COLUMN OBJREFERENCE.OBJECT_ID IS 'ссылка на экземпляр 1-го объекта ассоциативной связи с кратностью "много"';
COMMENT ON COLUMN OBJREFERENCE.REFERENCE IS 'ссылка на экземпляр 2-го объекта ассоциативной связи с кратностью "один"';


--objtype
--USER, LEGAL_PERSON, NATURAL_PERSON, CONTACT_NAME, ROLE, PERMISSION
INSERT INTO OBJTYPE (OBJECT_TYPE_ID, PARENT_ID, CODE, NAME, DESCRIPTION) 
VALUES (1, NULL, 'USER', 'Пользователь', NULL);

INSERT INTO OBJTYPE (OBJECT_TYPE_ID, PARENT_ID, CODE, NAME, DESCRIPTION) 
VALUES (2, 1, 'LEGAL_PERSON', 'Юр. лицо', NULL);

INSERT INTO OBJTYPE (OBJECT_TYPE_ID, PARENT_ID, CODE, NAME, DESCRIPTION) 
VALUES (3, 1, 'NATURAL_PERSON', 'Физ. лицо', NULL);

INSERT INTO OBJTYPE (OBJECT_TYPE_ID, PARENT_ID, CODE, NAME, DESCRIPTION) 
VALUES (4, 2, 'CONTACT_NAME', 'Контактное лицо', 'Контактное лицо для юридического лица');

INSERT INTO OBJTYPE (OBJECT_TYPE_ID, PARENT_ID, CODE, NAME, DESCRIPTION) 
VALUES (5, NULL, 'ROLE', 'Роль', NULL);

--COUNTRY -> STATE_PROVINCE -> CITY -> DEPARTMENT
INSERT INTO OBJTYPE (OBJECT_TYPE_ID, PARENT_ID, CODE, NAME, DESCRIPTION) 
VALUES (7, NULL, 'COUNTRY', 'Страна', NULL);

INSERT INTO OBJTYPE (OBJECT_TYPE_ID, PARENT_ID, CODE, NAME, DESCRIPTION) 
VALUES (8, 7, 'STATE_PROVINCE', 'State', 'Территориальная область в которой находится город');

INSERT INTO OBJTYPE (OBJECT_TYPE_ID, PARENT_ID, CODE, NAME, DESCRIPTION) 
VALUES (9, 8, 'CITY', 'Город', NULL);

INSERT INTO OBJTYPE (OBJECT_TYPE_ID, PARENT_ID, CODE, NAME, DESCRIPTION) 
VALUES (10, 9, 'DEPARTMENT', 'Отдел', NULL);

--SCHEDULE
INSERT INTO OBJTYPE (OBJECT_TYPE_ID, PARENT_ID, CODE, NAME, DESCRIPTION) 
VALUES (11, NULL, 'SCHEDULE', 'Рассаписание маршрутов', NULL);

--CURRENCY
INSERT INTO OBJTYPE (OBJECT_TYPE_ID, PARENT_ID, CODE, NAME, DESCRIPTION) 
VALUES (12, NULL, 'CURRENCY', 'Валюта', NULL);

--ORDER, ORDER_STATUS, CARGO, ORDER_ROADMAP, ORDER_STATUS, DEPT_STATUS, PAYMENT
INSERT INTO OBJTYPE (OBJECT_TYPE_ID, PARENT_ID, CODE, NAME, DESCRIPTION) 
VALUES (13, NULL, 'ORDER_STATUS', 'Статус заказа', NULL);

INSERT INTO OBJTYPE (OBJECT_TYPE_ID, PARENT_ID, CODE, NAME, DESCRIPTION) 
VALUES (14, NULL, 'ORDER', 'Заказ', NULL);

INSERT INTO OBJTYPE (OBJECT_TYPE_ID, PARENT_ID, CODE, NAME, DESCRIPTION) 
VALUES (15, 14, 'CARGO', 'Груз', NULL);

INSERT INTO OBJTYPE (OBJECT_TYPE_ID, PARENT_ID, CODE, NAME, DESCRIPTION) 
VALUES (16, 14, 'PAYMENT', 'Платеж', NULL);

INSERT INTO OBJTYPE (OBJECT_TYPE_ID, PARENT_ID, CODE, NAME, DESCRIPTION) 
VALUES (17, 14, 'ORDER_WAYPOINT', 'Путевая точка', NULL);

INSERT INTO OBJTYPE (OBJECT_TYPE_ID, PARENT_ID, CODE, NAME, DESCRIPTION) 
VALUES (18, NULL, 'ORDER_WAYPOINT_STATUS', 'Статус', 'Статус заказа в путевой точке');

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

--ATTRTYPE
--USER
INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (1, 1, NULL, 'EMAIL', 'E-mail пользователя');

INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (48, 1, NULL, 'PASSWORD', 'Пароль');

INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (2, 1, 5, 'ROLE', 'Роль');

--LEGAL_PERSON
INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE, NAME) 
VALUES (3, 2, NULL, 'TITLE', 'Название компании');

--NATURAL_PERSON
INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (4, 3, NULL, 'FULL_NAME', 'ФИО физ. лица');

INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,  NAME) 
VALUES (5, 3, NULL, 'TEL', 'Телефон физ. лица');

--CONTACT_NAME
INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE, NAME) 
VALUES (6, 4, NULL, 'FULL_NAME', 'ФИО контактного лица юр. лица');

INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (7, 4, NULL, 'TEL', 'Телефон контактного лица юр. лица');

INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (8, 4, NULL, 'EMAIL', 'E-mail контактного лица юр. лица');

--ROLE
INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (9, 5, NULL, 'ROLE_TITLE', 'Роль');

--COUNTRY
INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (11, 7, NULL, 'COUNTRY_TITLE', 'Страна');

--STATE_PROVINCE
INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (12, 8, NULL, 'STATE_TITLE', 'State');

--CITY
INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (13, 9, NULL, 'CITY_TITLE', 'Город');

--DEPARTMENT
INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (14, 10, NULL, 'ADDRESS', 'Адрес');

INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (15, 10, NULL, 'POSTAL_CODE', 'Индекс');

--SCHEDULE
INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (16, 11, 10, 'ORIGIN_DEPT', 'Точка отправления');

INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (17, 11, 10, 'DEST_DEPT', 'Точка прибытия');

INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (18, 11, NULL, 'PRIME_COST', 'Себестоимость маршрута');

INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (19, 11, NULL, 'SELLING_RATE', 'Наша накрутка');

INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (20, 11, NULL, 'DEPARTURE_DATE', 'Дата отправления');

INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (50, 11, NULL, 'ARRIVAL_DATE', 'Дата прибытия');

INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (21, 11, NULL, 'TARRIF_DATE', 'Дата тарифа');

INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (22, 11, 2, 'TRANSPORT_COMPANY', 'Транспортная компания');

INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (49, 11, 12, 'CURRENCY', 'Валюта');

--CURERNCY
INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (23, 12, NULL, 'CURRENCY_ID', 'Валюта');
INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (51, 12, NULL, 'CURRENCY_RATE', 'Коэф. относ. доллара');

--ORDER_STATUS
INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (24, 13, NULL, 'ORDER_STATUS_TITLE', 'Статус заказа');

--ORDER
INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (25, 14, NULL, 'START_DATE', 'Дата заказа');

INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (26, 14, NULL, 'DELIVERY_DATE', 'Дата доставки');

INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (27, 14, NULL, 'COMMENT', 'Комментарий');

INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (28, 14, 1, 'SENDER_USER', 'Отправитель');

INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (29, 14, 1, 'RECEIVER_USER', 'Получатель');

INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (30, 14, 13, 'ORDER_STATUS', 'Статус заказа');

--CARGO
INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (31, 15, NULL, 'CARGO_TITLE', 'Название');

INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (32, 15, NULL, 'WEIGHT', 'Вес');

INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (33, 15, NULL, 'HEIGHT', 'Высота');

INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (34, 15, NULL, 'WIDTH', 'Ширина');

INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (35, 15, NULL, 'DEPTH', 'Глубина');

INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (36, 15, NULL, 'COST', 'Ценность');

INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (37, 15, 12, 'CURRENCY', 'Валюта');

--PAYMENT
INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (38, 16, NULL, 'INCOMMING_DATE', 'Дата платежа');

INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (39, 16, NULL, 'AMOUNT', 'Сумма платежа');

INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (40, 16, NULL, 'CURRENCY', 'Валюта');

--ORDER_WAYPOINT
INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (42, 17, 10, 'DEPT', 'Отдел');

INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (43, 17, NULL, 'PRIORITY', 'Очередность');

INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (44, 17, 18, 'ORDER_WAYPOINT_STATUS', 'Статус заказа в путевой точке');

INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (45, 17, NULL, 'SENT_DATE', 'Дата отправки');

INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (46, 17, NULL, 'RECEIVED_DATE', 'Дата прибытия');

--ORDER_WAYPOINT_STATUS
INSERT INTO ATTRTYPE (ATTR_ID, OBJECT_TYPE_ID, OBJECT_TYPE_ID_REF, CODE,NAME) 
VALUES (47, 18, NULL, 'ORDER_WAYPOINT_STATUS', 'Статус заказа в отделении');

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

--ORDER_STATUS
INSERT ALL 
    INTO OBJECTS (OBJECT_ID, PARENT_ID, OBJECT_TYPE_ID, NAME, DESCRIPTION) 
    VALUES (SEQ_OBJECT_ID.NEXTVAL, NULL, 13, ORDER_STATUS_TITLE, NULL)    

    INTO ATTRIBUTES (ATTR_ID, OBJECT_ID, VALUE, DATE_VALUE)
    VALUES (24, SEQ_OBJECT_ID.CURRVAL, ORDER_STATUS_TITLE, NULL)    
SELECT ORDER_STATUS_TITLE FROM ORDER_STATUS;

--ORDER_WAYPOINT_STATUS
INSERT ALL
    INTO OBJECTS (OBJECT_ID, PARENT_ID,OBJECT_TYPE_ID, NAME, DESCRIPTION)
    VALUES (SEQ_OBJECT_ID.NEXTVAL, NULL, 18, DEPT_STATUS_TITLE, NULL)

    INTO ATTRIBUTES (ATTR_ID, OBJECT_ID, VALUE, DATE_VALUE)
    VALUES (47, SEQ_OBJECT_ID.CURRVAL, DEPT_STATUS_TITLE, NULL)

SELECT DEPT_STATUS_ID, DEPT_STATUS_TITLE FROM DEPT_STATUS;


--COUNTRY
INSERT ALL 
    INTO OBJECTS (OBJECT_ID, PARENT_ID,OBJECT_TYPE_ID, NAME, DESCRIPTION)
    VALUES (SEQ_OBJECT_ID.NEXTVAL, NULL, 7, COUNTRY_TITLE, NULL)

    INTO ATTRIBUTES (ATTR_ID, OBJECT_ID, VALUE, DATE_VALUE)
    VALUES (11, SEQ_OBJECT_ID.CURRVAL, COUNTRY_TITLE, NULL)

SELECT COUNTRY_TITLE FROM COUNTRY;

--STATE_PROVINCE
INSERT ALL 
    INTO OBJECTS (OBJECT_ID, PARENT_ID,OBJECT_TYPE_ID, NAME, DESCRIPTION)
    VALUES (SEQ_OBJECT_ID.NEXTVAL, COUNTR_OBJ_ID, 8, STATE_TITLE, NULL)

    INTO ATTRIBUTES (ATTR_ID, OBJECT_ID, VALUE, DATE_VALUE)
    VALUES (12, SEQ_OBJECT_ID.CURRVAL, STATE_TITLE, NULL)

SELECT STATE_TITLE, COUNTR.OBJECT_ID COUNTR_OBJ_ID
FROM COUNTRY C, STATE_PROVINCE SP, OBJECTS COUNTR
WHERE C.COUNTRY_ID=SP.COUNTRY_ID
AND C.COUNTRY_TITLE=COUNTR.NAME;

--CITY
INSERT ALL 
    INTO OBJECTS (OBJECT_ID, PARENT_ID,OBJECT_TYPE_ID, NAME, DESCRIPTION)
    VALUES (SEQ_OBJECT_ID.NEXTVAL, STATE_OBJ_ID, 9, CITY_TITLE, NULL)

    INTO ATTRIBUTES (ATTR_ID, OBJECT_ID, VALUE, DATE_VALUE)
    VALUES (13, SEQ_OBJECT_ID.CURRVAL, CITY_TITLE, NULL)

SELECT CITY_TITLE, STATE.OBJECT_ID STATE_OBJ_ID
FROM CITY C, STATE_PROVINCE SP, OBJECTS STATE
WHERE C.STATE_ID=SP.STATE_ID
AND SP.STATE_TITLE=STATE.NAME;

--DEPARTMENT
INSERT ALL 
    INTO OBJECTS (OBJECT_ID, PARENT_ID,OBJECT_TYPE_ID, NAME, DESCRIPTION)
    VALUES (SEQ_OBJECT_ID.NEXTVAL, CITY_OBJ_ID, 10, ADDRESS||' '||POSTAL_CODE||' '||CITY_TITLE, NULL)

    INTO ATTRIBUTES (ATTR_ID, OBJECT_ID, VALUE, DATE_VALUE)
    VALUES (14, SEQ_OBJECT_ID.CURRVAL, ADDRESS, NULL)
    
    INTO ATTRIBUTES (ATTR_ID, OBJECT_ID, VALUE, DATE_VALUE)
    VALUES (15, SEQ_OBJECT_ID.CURRVAL, POSTAL_CODE, NULL)

SELECT D.DEPT_ID, D.ADDRESS, D.POSTAL_CODE, C.CITY_TITLE, OBJ_CITY.OBJECT_ID CITY_OBJ_ID
FROM DEPARTMENT D, CITY C, OBJECTS OBJ_CITY
WHERE D.CITY_ID = C.CITY_ID
AND CITY_TITLE = OBJ_CITY.NAME
AND OBJ_CITY.OBJECT_TYPE_ID = 9;

COMMIT;